Member Avatar for joeltonnberg

Hey,
I've just started using mysql and I have som problem with JOIN.

I have table1 and table2 and I would like to join these two on table1.col1 and table2.col1 to eliminate all identical rows. In addition I need to have a WHERE statement on table1.

This is my attempt:

"SELECT * FROM table1 WHERE col1.var1 = 'value' JOIN table2 ON table1.col1=table2.col1"
  • 3 Contributors
  • 2 Replies
  • 120 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by saurav.prasad28

Dani AI

Generated

Quick correction for : the WHERE clause must come after the JOIN/ON part — SQL expects the FROM/JOIN section before WHERE. The snippet shown also uses col1.var1, which is not standard SQL notation (probably meant table1.col1 = 'value'). Qualify columns when both tables share names to avoid ambiguity.

A correct pattern with aliases and deduplication looks like this:

SELECT DISTINCT t1.col1, t1.colA, t2.colB
FROM table1 AS t1
JOIN table2 AS t2
  ON t1.col1 = t2.col1
WHERE t1.colA = 'value';

To preserve rows from table1 even when there is no match in table2, use LEFT JOIN and keep any conditions on table2 inside the ON clause; placing them in WHERE will filter out the NULLs and effectively turn the LEFT JOIN into an INNER JOIN:

SELECT t1.col1, t1.colA, t2.colB
FROM table1 AS t1
LEFT JOIN table2 AS t2
  ON t1.col1 = t2.col1 AND t2.active = 1
WHERE t1.colA = 'value';

As hinted, choose INNER vs LEFT based on whether unmatched table1 rows should be kept. As showed, select only needed columns rather than * for clarity and performance. If duplicate rows appear, they usually mean multiple matching rows exist in the joined table — resolve that by fixing keys, using DISTINCT, or aggregating (GROUP BY or a subquery) depending on the intended result. For syntax and deeper behavior, see the MySQL JOIN reference: MySQL JOIN Syntax. Use EXPLAIN and proper indexes on join columns to inspect and optimize performance.

Recommended Answers

All 2 Replies

Member Avatar for affnetguru

do something like this (not exactly like this)

"SELECT * FROM table1 LEFT JOIN table2 ON table1.col1=table2.col2 WHERE table1.col1=$var1";
Member Avatar for saurav.prasad28

Hi

suppose i have two table book and authors

select * from book;

"book_id" "title" "first_name" "description" "publisher_id"
"1" "Let us c" "Graham" "1" "1"
"2" "Maths" "greee" "1" "2"
"3" "Oparating system" "prasad" "39" "3"
"4" "Micro processor" "jamej" "0" "4"
"5" "Micro processor" "robert" \N "5"

select * from authors;

"book_id" "author_last" "first_name" "country"
"1" "Greene" "Graham" "United Kingdom"
"2" "graham" "greee" "germany"
"3" "saurav" "prasad" "India"

There is two table and you have to retrived only three column for both the table
for example you have FETCHed title and first_name from "book" and in "author" you have retrived only country
column then your querry will be like this

select book.title,book.first_name,authors.country from
book left join authors On book.book_id = authors.book_id

and for your example you used a where clause it is not like that see the querry below here i use where command also

select book.title,book.first_name,authors.country from
book left join authors On book.book_id = authors.book_id where book.first_name = 'graham'

MARKED AS SOLVED IF IT'S HELP YOU.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.