MortHub MortHub - 6 months ago 22
MySQL Question

Get info using table Joins with 2 MySQL tables and DISTINCT

I have two tables: the 'bidders' and 'solditems' tables.

The solditems table has 2 columns I need to use: buyerid and paidstatus.
In the bidders table, I want to get the info from columns: bidnum, bidfname, bidlname, bidphnum. (the 'buyerid' values in sold items corresponds with 'bidnum' in bidders)

I'm trying to get unique buyer numbers from the solditems table with paidstatus marked as unpaid, and then get those buyers' info (fname, lname, and phnum) from the bidders table.




This is what I have right now:

SELECT
DISTINCT(i.buyerid),
b.bidfname,
b.bidlname,
b.bidphnum
FROM 'solditems' AS i
INNER JOIN 'bidders' AS b ON i.buyerid = b.bidnum
WHERE i.paidstatus='unpaid'
ORDER BY i.buyerid ASC


If I use that in phpmyadmin sql section to test it, I get an error that says:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''solditems' AS i INNER JOIN 'bidders' AS b ON i.buyerid = b.bidnum WHERE i.paids' at line 1



I've never done any joins before but I just can't seem to get this working.

Answer

Changed ' to ` symbol for columns and ' to " for string values

SELECT 
     DISTINCT(i.buyerid), 
     b.bidfname, 
     b.bidlname, 
     b.bidphnum FROM `solditems` AS i 
INNER JOIN `bidders` AS b ON i.buyerid = b.bidnum 
WHERE i.paidstatus= "unpaid"
ORDER BY i.buyerid ASC