logo
down
shadow

I need to select clients who have multiple records in one column


I need to select clients who have multiple records in one column

By : Teja Bobbba
Date : October 21 2020, 06:10 AM
Hope this helps I'm tasked with selecting all clients who have traveled in 2017 and 2018, but not 2019. , Use correlated subquery with exists and not exists
code :
select clientid from cust a
where exist
  (select 1 from cust b where a.clientid=b.clientid and tour_date  between '2017-01-01' and '2017-12-31')
and exist 
  (select 1 from cust b where a.clientid=b.clientid and tour_date  between '2018-01-01' and '2018-12-31')
and not exists
   (select 1 from cust b where a.clientid=b.clientid and tour_date  between '2019-01-01' and '2019-12-31')


Share : facebook icon twitter icon
How to select multiple records (row) from a column in mysql?

How to select multiple records (row) from a column in mysql?


By : Edarkx
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further You can do either one, but the IN query is much more efficient for this purpose for any large queries. I did some simple testing long ago that revealed it's about 10 times faster to use the IN construct for this. If you're asking if the syntax is correct then yes, it looks fine, other than missing semi-colons to complete the statement.
EDIT: It looks like the actual question you were asking was "why do these queries only return one value". Well, looking at the sample code you posted, the problem is here:
code :
$fetch = mysql_fetch_assoc($result) or die (mysql_error());
$itemsCollected = $fetch['item_name'];
echo $itemsCollected;
$sql = "SELECT item_name from items WHERE item_id IN('s001','a012')";
$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
//       then create $userid, $fullname, and $userstatus
while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["fullname"];
    echo $row["userstatus"];
}

mysql_free_result($result);
Select Records With Only One Value In A Column Where Multiple Are Possible

Select Records With Only One Value In A Column Where Multiple Are Possible


By : Shiva
Date : March 29 2020, 07:55 AM
To fix the issue you can do In standard (ANSI/ISO) SQL, a derived table is your friend. Here, we join the customer table against a derived table that produces the list of customers having only 1
code :
select *
from customer c
join ( select customer_id
       from customer
       group by customer_id
       having count(program_name) = 1
     ) t on t.customer_id = c.customer_id
where ... -- any further winnowing of the result set occurs here
Select multiple records grouped by primary key with max value on a column

Select multiple records grouped by primary key with max value on a column


By : Sushil Kokate
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I have a table with the following structure (dummy column names and data): , Isn't this just a plain group by?
code :
SELECT a, b, c, d, MAX(issue)
FROM tablename
GROUP BY a, b, c, d
SELECT a, b, c, d, issue, contents
FROM tablename t1
WHERE issue = (select max(issue) from tablename t2
               where t1.a = t2.a
                 and t1.b = t2.b
                 and t1.c = t2.c
                 and t1.d = t2.d)
MS SQL How to select clients where last N records are the same

MS SQL How to select clients where last N records are the same


By : user3410858
Date : March 29 2020, 07:55 AM
With these it helps I have a table that is consisted of ClientID, ActionDate, Action and Result. , 1.
code :
select ClientID from
(
select 
  ClientID,
  Action,
  Result,
  row_number() over (partition by ClientID order by ActionDate desc) as num
from ClientActions
) T -- the latest 5 action/result
where rnum <= 5
group by ClientID
having MAX(case when Action='Call' and Result = 'NoAnswer' then 0 else 1 end) = 0
select ClientID
from ClientActions
where DATEDIFF(DAY, ActionDate, GETDATE()) <= 30
group by ClientID
having MAX(case when Action='Call' and Result = 'NoAnswer' then 0 else 1 end) = 0
select same records multiple times with one column value changed in SQL

select same records multiple times with one column value changed in SQL


By : roqueper
Date : March 29 2020, 07:55 AM
I wish this help you I have getting same records multiple times with one column value changed using union like this. , I simple CROSS JOIN with the specified VALUES should do the trick
code :
Select Col1,Col2,Col3
 From  YourTable A
 Cross Join (Values ('A'),('B'),('C') ) B (Col3)
Related Posts Related Posts :
  • Combining the UNIQUE and CHECK constraints
  • MS SQL Server decimal data type rounds up
  • sp_addlinkedserver on sql server 2005 giving problem
  • Transitive SQL query on same table
  • Strange use of the index in Mysql
  • Oracle - pl sql selecting from SYS_REFCURSOR
  • Broken count(*) after adding LEFT JOIN
  • Product Catalog Schema design
  • ODBC Connection String Problem
  • What's a reasonable number of rows and tables to be able to join in MySQL?
  • CFQuery where param
  • Split a Column in SQL Server
  • What does the % symbol do at the end of a string in SQL?
  • sql server 2005 express - Invalid Object Name error
  • Merge overlapping date intervals
  • Loop Control within a DataflowTask in ETL
  • SQL | How to always round up regardless of the last integer value, even when that may be 0
  • how to convert any char to - in sql server?
  • how to create function in postgres
  • Need to speed up the results of this SQL statement. Any advice?
  • How do I retrieve 10 days data up to date 'XYZ' from mysql with Perl when some days data are missing, i.e. public holida
  • How to export data with Oracle SQL Developer?
  • Aggregate SQL column values by time period
  • Are soft deletes a good idea?
  • DBCC SHRINKFILE 1 sproc for multiple databases
  • SQL VBA syntax issue
  • How to select a subset of results from a select statement
  • Oracle SQL: ROLLUP not summing correctly
  • How to select the second row in a table without where clause
  • How to compare the date in SQL
  • Sql Server xml column with Entity Framework - how to keep insignificant whitespaces
  • Finding all related tables to a given table
  • Two different tables or just one with bool column?
  • Rotating sql table
  • How i can execute function?
  • How to write an error message from an BCP operation to a log file and stop processing the batch file.
  • Does MySQL have an equivalent of SQL Server "indexed views"?
  • Can i use MAX function for each tuple in the retrieved data set
  • Is there a way to make Identity Specification show up in the table designer next to the Allow Nulls column?
  • How to pass a date in query
  • oracle sql listagg
  • how to use a parameterized function for the Default Binding of a Sql Server column
  • Cassandra/HBase or just MySQL: Potential problems doing the next thing
  • 32bit to 64bit sql server 2008 database conversion
  • How to count on DISTINCT ON without subquery
  • SQL: changing IN to EXISTS
  • I need to convert this string to datetime in SQL Server
  • noSQL/SQL/RoR: Trying to build scalable ratings table for the game
  • Should I include user_id in multiple tables?
  • sql in ms-access
  • Which data type saves more space TINYTEXT or VARCHAR for variable data length in MySQL?
  • Select Max number with another column
  • SQL: Having a primary key also be a foreign key
  • PostgreSQL String search for partial patterns removing exrtaneous characters
  • sql query for fetching 10 rows with near values
  • Temporary intermediate table
  • Finding employees specific to department in SQL Server 2000
  • sql like with another table
  • mysql Command timeout error
  • SQL Server encrypt mdf file
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk