CODEWITHSUNDEEP
sqlsqliteselectjoin
kkk
kkk

Reputation: 112

SQL join, get data from one table

I have a question about joins. How to get all Order of Customer with a specified name.

In my result I want only have all columns from "Orders" table and zero columns from "Customers" table. Here you can see tables: https://www.w3schools.com/sql/sql_join.asp

SELECT *
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID=Customers.CustomerID
WHERE CustomerName = "Wilman Kala"

I tested this here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join It returns all columns from two tables.

Thanks in advance for a help.

Upvotes: 2

Views: 2451

Answers (3)

juergen d
juergen d

Reputation: 204756

Add the table name before the *.

SELECT orders.*
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID=Customers.CustomerID
WHERE CustomerName = "Wilman Kala"

Or better name every single column you want to select

select orders.col1, orders.col2, ...

Upvotes: 4

Ilyes
Ilyes

Reputation: 14928

Try:

SELECT O.*
FROM Orders O
LEFT JOIN Customers C ON O.CustomerID=C.CustomerID
WHERE C.CustomerName = 'Wilman Kala'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269613

If you only want columns from Orders, you might consider IN or EXISTS:

SELECT o.*
FROM Orders o
WHERE EXISTS (SELECT 1
              FROM Customers c
              WHERE o.CustomerID = c.CustomerID AND
                    c.CustomerName = 'Wilman Kala'
             );

This does something slightly different from the JOIN version. If two customers have the same name, this will return each order only once. In a version with JOIN, the orders will be duplicated if the names are duplicated.

Usually, the non-duplication behavior is what you want.

Upvotes: 1

Related Questions