Trouble creating SQL queries - Beginner-CodePudding

I've created the linked ERD for some university work but SQL is the one thing that just hasn't been sticking. I am required to list the id, first name, last name, telephone and address of all members who have made more than one purchase, ensuring I add an appropriate alias when retrieving the details for all members.

ERD link - [1]: https://i.stack.imgur.com/AD6C0.png

My attempt is below. Is this correct? Can I add individual aliases to each member detail? Can I use "WHERE purchaseTotal" if purchases aren't a part of the member table?

SELECT (memberId AS 'ID', memberFirstName AS 'First Name', memberLastName AS 'Last Name',
        memberAddress AS 'Address', memberPhone AS 'Phone')
FROM Member
WHERE purchaseTotal > 1;

CodePudding user response：

In very basic terms, you can use only that column in a query, which is present in the tables being queried.

So in your query above, you are using purchaseTotal column, but that is not available in Member table. So you need to add Purchases table as well.

The query should be

SELECT Members.memberId AS 'ID', memberFirstName AS 'First Name', memberLastName AS 'Last Name', memberAddress AS 'Address', memberPhone AS 'Phone'
FROM Purchases
LEFT JOIN Members ON Purchases.memberId = Members.memberId
WHERE purchaseTotal > 1;

Note :

For all the columns that are common in both the tables, you need to specify from which table you need to display the column. Ex, Members.memberId

CodePudding user response：

You are running to the column named purchaseTotal but you are not looking for it in the correct table. purchaseTotal belong to Purchases table.

if you want to get a value from purchaseTotal column, you should use correct table.

select * from purchases where purchaseTotal > 1;

Or you should use "join":

SELECT *
FROM Member
INNER JOIN Purchases ON Purchases.memberID=Member.memberID
WHERE Purchases.purchaseTotal > 1;