I know how to select the values form one table and if they have matching values in another
SELECT *
FROM me_cards AS c LEFT JOIN
me_member_cards AS m
ON m.cardID = c.id AND m.userID = 2 AND m.status = 'Collecting' LEFT JOIN
me_decks AS d
ON d.deckName = c.deckName
WHERE c.deckName = 'Couples'
ORDER BY cardNumber
Now the problem i'm having is if there are multiple records in the second table it gives mutlitple records
I have the two tables
If the card appears multiple times in member cards then the query returns all rows, i want it to return all the cards and if the user has that card. Current, id the user has the card then the details from me_member_cards show, if they don't it shows as null
Table details
CREATE TABLE `me_cards` (
`id` int(11) NOT NULL,
`deckName` varchar(255) NOT NULL,
`cardNumber` varchar(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `me_cards` (`id`, `deckName`, `cardNumber`) VALUES
(202, 'Couples', '01'),
(203, 'Couples', '02'),
(204, 'Couples', '03'),
(205, 'Couples', '04'),
(206, 'Couples', '05'),
(207, 'Couples', '06'),
(208, 'Couples', '07'),
(209, 'Couples', '08'),
(210, 'Couples', '09'),
(211, 'Couples', '10'),
(212, 'Couples', '11'),
(213, 'Couples', '12'),
(214, 'Couples', '13'),
(215, 'Couples', '14'),
(216, 'Couples', '15'),
(217, 'Couples', '16'),
(218, 'Couples', '17'),
(219, 'Couples', '18'),
(220, 'Couples', '19'),
(221, 'Couples', '20')
CREATE TABLE `me_decks` (
`deckName` varchar(255) NOT NULL,
`deckDescription` varchar(255) NOT NULL,
`deckCategory` varchar(255) NOT NULL,
`deckSubCat` varchar(255) NOT NULL,
`deckType` varchar(255) NOT NULL,
`deckFeatures` varchar(255) NOT NULL,
`deckWorth` int(1) NOT NULL,
`deckCardAmount` int(4) NOT NULL,
`deckURLName` varchar(255) NOT NULL,
`deckReleased` tinyint(4) NOT NULL,
`deckOrder` int(5) NOT NULL,
`deckCard` varchar(3) NOT NULL DEFAULT '01'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `me_decks`
--
INSERT INTO `me_decks` (`deckName`, `deckDescription`, `deckCategory`, `deckSubCat`, `deckType`, `deckFeatures`, `deckWorth`, `deckCardAmount`, `deckURLName`, `deckReleased`, `deckOrder`, `deckCard`) VALUES
('Couples', 'Senshi & Shitennout Pair', 'Puzzle', 'Manga', 'Puzzle', '', 1, 20, 'couples', 1, 999, '14');
CREATE TABLE `me_member_cards` (
`id` int(11) NOT NULL,
`userID` int(11) NOT NULL,
`cardID` int(11) NOT NULL,
`status` varchar(16) NOT NULL DEFAULT 'undefined'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `me_member_cards`
--
INSERT INTO `me_member_cards` (`id`, `userID`, `cardID`, `status`, `id`, `deckName`, `cardNumber`) VALUES
(29, 2, 212, 'Collecting', 212, 'Couples', '11'),
(80, 2, 221, 'Collecting', 221, 'Couples', '20'),
(81, 2, 221, 'Collecting', 221, 'Couples', '20'),
(83, 2, 221, 'Collecting', 221, 'Couples', '20'),
(101, 2, 221, 'Collecting', 221, 'Couples', '20');
What i am getting is
id deckName cardNumber id userID cardID status deckName deckDescription deckCategory deckSubCat deckType deckFeatures deckWorth deckCardAmount deckURLName deckReleased deckOrder deckCard 202 Couples 01 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 203 Couples 02 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 204 Couples 03 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 205 Couples 04 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 206 Couples 05 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 207 Couples 06 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 208 Couples 07 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 209 Couples 08 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 210 Couples 09 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 211 Couples 10 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 212 Couples 11 29 2 212 Collecting Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 213 Couples 12 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 214 Couples 13 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 215 Couples 14 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 216 Couples 15 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 217 Couples 16 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 218 Couples 17 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 219 Couples 18 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 220 Couples 19 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 221 Couples 20 81 2 221 Collecting Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 221 Couples 20 83 2 221 Collecting Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 221 Couples 20 101 2 221 Collecting Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 221 Couples 20 80 2 221 Collecting Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14
Add what i want is
id deckName cardNumber id userID cardID status deckName deckDescription deckCategory deckSubCat deckType deckFeatures deckWorth deckCardAmount deckURLName deckReleased deckOrder deckCard 202 Couples 01 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 203 Couples 02 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 204 Couples 03 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 205 Couples 04 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 206 Couples 05 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 207 Couples 06 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 208 Couples 07 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 209 Couples 08 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 210 Couples 09 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 211 Couples 10 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 212 Couples 11 29 2 212 Collecting Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 213 Couples 12 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 214 Couples 13 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 215 Couples 14 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 216 Couples 15 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 217 Couples 16 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 218 Couples 17 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 219 Couples 18 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 220 Couples 19 NULL NULL NULL NULL Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14 221 Couples 20 81 2 221 Collecting Couples Senshi & Shitennout Pair Puzzle Manga Puzzle 1 20 couples 1 999 14
What i ultimately want to do is get all the cards in the deck, if the user has the card then display it, if they don't then show a placeholder
You want to know for every card in the deck, whether a particular user has this card, and if so show me_member_cards details. The problem is that a user can have a card multiple times, but you still want to show only one result row for the card. So you must decide which me_member_cards data to show in that case. Here is an example where I show the maximum status per card:
select *
from me_decks d
join me_cards c on c.deckname = d.deckname
left join
(
select cardid, max(status) as max_status
from me_member_cards
where userid = 2
and status = 'Collecting'
group by cardid
) mc on mc.cardid = c.id
where d.deckname = 'Couples';
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments